package com.lxy;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel文件读取工具类
 * 用于读取xlsx文件并进行数据筛选和处理
 */
public class ExcelReader {
    
    /**
     * 读取Excel文件并筛选指定列的数据
     * @param filePath Excel文件路径
     * @param filterColumn 筛选列的索引（从0开始）
     * @param filterValue 筛选的值
     * @return 筛选后的数据列表，每行数据用Map表示
     */
    public static List<Map<String, Object>> readAndFilterExcel(String filePath, int filterColumn, String filterValue) {
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取表头行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                System.out.println("Excel文件为空或没有表头");
                return result;
            }
            
            // 存储表头信息
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getCellValueAsString(cell));
            }
            
            // 遍历数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                
                // 获取筛选列的值
                Cell filterCell = row.getCell(filterColumn);
                String cellValue = getCellValueAsString(filterCell);
                
                // 如果筛选列的值匹配，则添加到结果中
                if (filterValue.equals(cellValue)) {
                    Map<String, Object> rowData = new HashMap<>();
                    
                    // 读取该行所有列的数据
                    for (int j = 0; j < headers.size(); j++) {
                        Cell cell = row.getCell(j);
                        String columnName = headers.get(j);
                        Object value = getCellValue(cell);
                        rowData.put(columnName, value);
                    }
                    
                    result.add(rowData);
                }
            }
            
        } catch (IOException e) {
            System.err.println("读取Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
        
        return result;
    }
    
    /**
     * 获取指定筛选条件下某列的所有数据
     * @param filePath Excel文件路径
     * @param filterColumn 筛选列的索引（从0开始）
     * @param filterValue 筛选的值
     * @param targetColumn 目标列的索引（从0开始）
     * @return 目标列的数据列表
     */
    public static List<Object> getColumnData(String filePath, int filterColumn, String filterValue, int targetColumn) {
        List<Object> columnData = new ArrayList<>();
        
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            
            // 遍历数据行（跳过表头）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                
                // 检查筛选条件
                Cell filterCell = row.getCell(filterColumn);
                String filterCellValue = getCellValueAsString(filterCell);
                
                if (filterValue.equals(filterCellValue)) {
                    // 获取目标列的数据
                    Cell targetCell = row.getCell(targetColumn);
                    Object value = getCellValue(targetCell);
                    columnData.add(value);
                }
            }
            
        } catch (IOException e) {
            System.err.println("读取Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
        
        return columnData;
    }
    
    /**
     * 读取Excel文件的所有数据
     * @param filePath Excel文件路径
     * @return 所有数据的列表
     */
    public static List<Map<String, Object>> readAllData(String filePath) {
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                return result;
            }
            
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getCellValueAsString(cell));
            }
            
            // 读取所有数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String columnName = headers.get(j);
                    Object value = getCellValue(cell);
                    rowData.put(columnName, value);
                }
                
                result.add(rowData);
            }
            
        } catch (IOException e) {
            System.err.println("读取Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
        
        return result;
    }
    
    /**
     * 获取单元格的值（保持原始数据类型）
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return null;
            default:
                return cell.toString();
        }
    }
    
    /**
     * 获取单元格的字符串值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 处理数字，避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return cell.toString();
        }
    }
    
    /**
     * 打印数据到控制台（用于调试）
     */
    public static void printData(List<Map<String, Object>> data) {
        if (data.isEmpty()) {
            System.out.println("没有找到匹配的数据");
            return;
        }
        
        System.out.println("找到 " + data.size() + " 条匹配的数据：");
        for (int i = 0; i < data.size(); i++) {
            System.out.println("第 " + (i + 1) + " 条数据：");
            Map<String, Object> row = data.get(i);
            for (Map.Entry<String, Object> entry : row.entrySet()) {
                System.out.println("  " + entry.getKey() + ": " + entry.getValue());
            }
            System.out.println();
        }
    }
}
